package fy.uap.dao;

import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;

import org.json.JSONArray;
import org.json.JSONObject;


import fy.config.FyConfig;
import fy.form.listen.set.OA_UAPAccountContrast;
import fy.util.DBUtil;

public class CheckProviderAndRangeDao {

	private String code;

	private JSONArray msg;

	public JSONArray getMsg() {
		return msg;
	}

	public String getCode() {
		return code;
	}

	public boolean checkProviderNameIsExist(String name) {
		Connection conn = null;
		PreparedStatement pst = null;
		ResultSet rs = null;
		boolean success = false;

		try {
			conn = DBUtil.connect(FyConfig.getUAP_DB_CONFIG());

			pst = conn
					.prepareStatement("select cvencode from fy_supplier_vendor where cvenname = ? and (dr='0' or dr is null) ");

			pst.setString(1, name);

			rs = pst.executeQuery();

			if (rs.next()) {
				code = rs.getString(1);
			}
			success = true;
		} catch (Exception e) {
			e.printStackTrace();
		} finally {
			DBUtil.close(rs);
			DBUtil.close(pst);
			DBUtil.close(conn);
		}
		return success;
	}

	public boolean checkProviderNameAndRange(String name, String typeNodes,
			String companyNodes, String providerCode) {
		Connection conn = null;
		PreparedStatement pst = null;
		ResultSet rs = null;
		boolean success = false;
		try {
			conn = DBUtil.connect(FyConfig.getUAP_DB_CONFIG());


			pst = conn
					.prepareStatement("select cvencode from fy_supplier_vendor where cvenname = ? and (dr='0' or dr is null) and denddate is null ");

			pst.setString(1, name);

			rs = pst.executeQuery();

			if (rs.next()) {
				code = rs.getString(1);
			}
			DBUtil.close(rs, pst);
			if (code == null || code.equals(providerCode)) {
				StringBuilder sb = new StringBuilder();
				sb.append("select i.pk_invclass, s.cinvclassname, i.pk_corp, i.pk_veninv, i.cvendorclass from fy_supplier_vendor_invclass i left join fy_supplier_vendor v on i.pk_vendor = v.pk_vendor left join fy_supplier_invclass s on i.pk_invclass = s.pk_invclass where v.cvencode=? and ( i.dr is null or i.dr = '0' ) and ( v.dr is null or v.dr = '0' ) and s.isleaf = 'Y' and (i.cvendorclass='3' or i.denddate is null or to_date(i.denddate,'yyyy-mm-dd') > to_date( to_char(sysdate, 'yyyy-mm-dd'), 'yyyy-mm-dd' )) and ( ");

				sb.append(" i.pk_invclass='").append(typeNodes.replaceAll(",", "' or i.pk_invclass='")).append("') and (");
				
				sb.append(changeToUapAccountID(companyNodes)).append(") ");

				pst = conn.prepareStatement(sb.toString());
				
				pst.setString(1, code);
				
				rs = pst.executeQuery();
				
				JSONArray ja = new JSONArray();
				
				while( rs.next() ){
					JSONObject jo = new JSONObject();
					jo.put("pk_invclass", rs.getString(1));
					jo.put("pk_corp", OA_UAPAccountContrast.getOAAccountID(rs.getString(3)).getId());
					jo.put("cvendorclass", rs.getString(5));
					ja.put(jo);
				}
				
				msg = ja;
				ja = null;
				System.out.println(msg);
			}
			success = true;
		} catch (Exception e) {
			e.printStackTrace();
		} finally {
			DBUtil.close(rs);
			DBUtil.close(pst);
			DBUtil.close(conn);
		}
		return success;
	}
	private String changeToUapAccountID(String companyNodes){
		StringBuilder sb = new StringBuilder();
		String[] nodes = companyNodes.split(",");
		for( int i = 0, length = nodes.length; i< length; i++ ){
			if( i !=  0){
				sb.append(" or ");
			}
			sb.append(" i.pk_corp='").append(OA_UAPAccountContrast.getUapAccountID(nodes[i])).append("' ");
		}
		return sb.toString();
	}
}
